package dal

import org.apache.log4j.Logger
import java.sql.*

class ExecuteQueryService {

    boolean transactional = false
    static Logger gLogger = Logger.getLogger(ExecuteQueryService.class);
    def grailsApplication
    DBConnectionService dbconnectionservice = new DBConnectionService()
    def setting

    void afterPropertiesSet() {
      this.setting = grailsApplication.config.setting
    }

    /**
     * to execute or process select queries
     * @param sqlQuery
     * @param paramList
     * @return collection in form of ArrayList<HashMap<String,Object>>
     */
    def executeSqlQuery(String sqlQuery,def paramList = []){
        gLogger.info("Coming into executeSqlQuery method of ExecuteQueryService");
        def rowList
		//ArrayList<Object> result = new ArrayList<Object>();
		rowList = new ArrayList<HashMap<String,Object>>();
		Connection lconn = null;
		ResultSet lrs = null;
		ResultSetMetaData lrsMetaData = null;
		HashMap<String,Object> lrowMap = null;
		gLogger.info("Param List "+paramList+"  sqlQuery   "+sqlQuery);

		PreparedStatement prepStmt = null;
        try{

          lconn = dbconnectionservice.getConnection()
          gLogger.info(lconn)

          prepStmt = lconn.prepareStatement(sqlQuery);
          if(paramList != null && !paramList.isEmpty())
          {
              for(int i=0;i<paramList.size();i++)
              {
                  Object obj = paramList.get(i);
                  if(obj instanceof Integer)
                      prepStmt.setInt(i+1, Integer.parseInt(obj.toString()));
                  else if(obj instanceof Long)
                    prepStmt.setLong(i+1, Long.parseLong(obj.toString()));
                  else if(obj instanceof String)
                      prepStmt.setString(i+1, (String)obj);
                  else if(obj instanceof Boolean)
                      prepStmt.setBoolean(i+1,Boolean.getBoolean(obj.toString()));
                  else if(obj instanceof java.sql.Date)
                      prepStmt.setDate(i+1, (java.sql.Date)obj);
                  else
                      prepStmt.setObject(i+1, DBTypes.NULL);
              }//end for
          }//end if
          lrs = prepStmt.executeQuery();
          if(lrs == null){
             return null;
          }

          lrsMetaData = lrs.getMetaData();
          int metaDataCount = lrsMetaData.getColumnCount();
          gLogger.info("metaDataCount"+metaDataCount);

          while(lrs.next()){

            lrowMap = new HashMap<String,Object>();
            Object[] obj = new Object[metaDataCount];
            for(int i=1;i<=metaDataCount;i++)
            {
              String columnLabel = lrsMetaData.getColumnLabel(i).toUpperCase();
              if(lrs.getObject(i)!=null)
              {
                  if(lrs.getObject(i) instanceof Clob)
                  {
                      Clob clob = (Clob)lrs.getObject(i);
                      char[] clobContent = new char[(int)clob.length()];
                      clob.getCharacterStream().read(clobContent);
                      lrowMap.put(columnLabel, clobContent);
                  }
                  else
                  {
                      lrowMap.put(columnLabel, lrs.getObject(i));
                  }
              }
              else
              {
                  lrowMap.put(columnLabel, null);
              }
              obj[i-1] = lrs.getObject(i);
            }
            //gLogger.info("lrowMap"+lrowMap);
            rowList.add(lrowMap);
            //result.add(obj);
          }//end while
      }catch(Exception e){
          gLogger.error("Exception in executeSqlQuery method of ExecuteQueryService",e);

      }

      gLogger.info("Result Size : ${rowList?.size()}") 
      gLogger.info("Out from executeSqlQuery method of ExecuteQueryService");
      return rowList
    }

}
